﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;
using System.Collections;
using System.Net.Mail;
using System.Drawing;
using System.Security.Permissions;
using Microsoft.Office.Interop;
using Microsoft.Office.Tools;
using Office = Microsoft.Office.Interop.Excel;
using System.Text;
using System.Runtime.InteropServices;
using System.Threading;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Excel;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn;
        private System.Data.DataTable dt_rep;
        private System.Data.DataTable[] dt_allrep;
        private delegate void Deleg();
        private Deleg del;
        private DateTime @date_b;                       //- Начало периода отчёта
        private DateTime @date_e;                       //- Окончание периода отчёта
        private System.Data.DataTable dt_pharm = new System.Data.DataTable();   //- Для выбора аптек
        private int flag_pharm = 0;                     //- 0 - без разбивки по аптекам, 1 - с разбивкой по аптекам
        private int flag_drug = 0;                      //- 0 - без разбивки по товарам, 1 - с разбивкой по товарам
        private int flag_proj = 0;                      //- 0 - без разбивки по проектам, 1 - с разбивкой по проектам
        private int flag_join = 0;                      //- 0 - объединённый по проектам, 1 - не объединённый
        private int flag_all = 0;                       //- 0 - общий отчёт по проектам, 1 - раздельный отчёт

        
        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void CreateExcel()
        {
            //- Формирование файла

        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
            this.Text = "Отчёт по дифференцированному ценообразованию";
            dateTimePicker1.Value = DateTime.Now.AddDays(-30);
            dateTimePicker2.Value = DateTime.Now.AddDays(-1);
            date_b = dateTimePicker1.Value;
            date_e = dateTimePicker2.Value;
            checkBox5.Checked = true;
            flag_all = 1;
            checkBox4.Checked = false;
            flag_join = 0;
            checkBox3.Checked = false;
            flag_proj = 0;
            checkBox2.Checked = true;
            flag_drug = 1;
            checkBox1.Checked = false;
            flag_pharm = 0;
            
        }

        private void StartCreateRep(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            dataGridView2.DataSource = null;
            dataGridView3.DataSource = null;
            dataGridView4.DataSource = null;
            del = new Deleg(CreateRep);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
            dataGridView2.Enabled = false;
            dataGridView3.Enabled = false;
            dataGridView4.Enabled = false;
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            dataGridView2.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            dataGridView3.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            dataGridView4.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            if (dt_allrep.Length == 4)
            {
                dt_allrep[0].TableName = "Город-сибирь плюс ЦК";
                dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_allrep[0].DefaultView));
                dt_allrep[1].TableName = "Город-сибирь минус ЦК";
                dataGridView2.Invoke((Func<object>)(() => dataGridView2.DataSource = dt_allrep[1].DefaultView));
                dt_allrep[2].TableName = "Старт-спецназ плюс ЦК";
                dataGridView3.Invoke((Func<object>)(() => dataGridView3.DataSource = dt_allrep[2].DefaultView));
                dt_allrep[3].TableName = "Старт-спецназ минус ЦК";
                dataGridView4.Invoke((Func<object>)(() => dataGridView4.DataSource = dt_allrep[3].DefaultView));
            }
            else
            {
                MessageBox.Show("На такой период нет проектов");
            }
        }

        private void CreateRep()
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            dt_allrep = new System.Data.DataTable[4];
            dt_allrep[0] = new System.Data.DataTable();
            dt_allrep[1] = new System.Data.DataTable();
            dt_allrep[2] = new System.Data.DataTable();
            dt_allrep[3] = new System.Data.DataTable();
            try
            {
                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[6];
                par[0].name = "@date_b";
                par[0].value = date_b;
                par[1].name = "@date_e";
                par[1].value = date_e;
                par[2].name = "@flag_pharm";
                par[2].value = flag_pharm;
                par[3].name = "@flag_drug";
                par[3].value = flag_drug;
                par[4].name = "@flag_proj";
                par[4].value = flag_proj;
                par[5].name = "@flag_all";
                par[5].value = @flag_all;
                dt_allrep = (new SqlStorProc()).SqlProcExecWithQuery(conn, "pas_DAC_DiffPrice", 300, par);
                conn.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                conn.Close();
            }


        }

        private SqlDataReader RepProc(SqlConnection sqlcon)//метод для вызова хранимки
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"sp_name"; //имя хранимой процедуры
            sqlcom.CommandTimeout = 300;

            

            return sqlcom.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "csv-file";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table[col_n[y], i].Value + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            //- Начало отчётного периода
            date_b = dateTimePicker1.Value;
        }

        private void dateTimePicker2_ValueChanged(object sender, EventArgs e)
        {
            //- Окончание отчётного периода
            date_e = dateTimePicker2.Value;
        }

        //private void button3_Click(object sender, EventArgs e)
        //{
        //    //- Фильтр по аптекам
        //    dt_pharm = null;
        //    try
        //    {
        //        PharmFilter.PharmFilter f3 = new PharmFilter.PharmFilter();

        //        if (dt_pharm == null)
        //        {
        //            SqlCommand myCommand = conn.CreateCommand();
        //            myCommand.CommandType = CommandType.StoredProcedure;
        //            myCommand.CommandText = @"pas_PharmSel";
        //            if (conn.State == ConnectionState.Closed) conn.Open();
        //            dt_pharm = new DataTable();
        //            dt_pharm.Load(myCommand.ExecuteReader());
        //            foreach (DataColumn dc in dt_pharm.Columns)
        //            {
        //                if (dc.ColumnName != namechcol) dc.ReadOnly = true;
        //                else dc.ReadOnly = false;
        //            }
        //            conn.Close();
        //        }
        //        f3.namechcol = namechcol;
        //        f3.Text = "Фильтр по аптекам";
        //        f3.sortcoumn = "Аптека";
        //        f3.dataGridView1.DataSource = dt_pharm.DefaultView;
        //        f3.ShowDialog();
        //        listBox1.Items.Clear();
        //        foreach (DataRow dr in dt_pharm.Rows)
        //        {
        //            if (Convert.ToBoolean(dr[namechcol]) == true)
        //            {
        //                listBox1.Items.Add(dr[f3.sortcoumn]);
        //            }
        //        }
        //        if (listBox1.Items.Count == 0) listBox1.Items.Add("(Все аптеки)");
        //        f3.Close();
        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBox.Show(ex.ToString());
        //    }
        //}

        private void button4_Click(object sender, EventArgs e)
        {
            //- Загрузка списка аптек
            Form2 fr = new Form2();
            fr.ShowDialog();

        }

        private void button5_Click(object sender, EventArgs e)
        {
            //- Загрузка коэффициентов
            Form3 fr = new Form3();
            fr.ShowDialog();
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            //- По аптекам
            if (checkBox1.Checked == true)
            {
                flag_pharm = 1;
            }
            else
            {
                flag_pharm = 0;
            }
        }

        private void checkBox2_CheckedChanged(object sender, EventArgs e)
        {
            //- По товарам
            if (checkBox2.Checked == true)
            {
                flag_drug = 1;
            }
            else
            {
                flag_drug = 0;
            }
        }

        private void checkBox3_CheckedChanged(object sender, EventArgs e)
        {
            //- По проектам
            if (checkBox3.Checked == true)
            {
                flag_proj = 1;
            }
            else
            {
                flag_proj = 0;
            }
        }

        private void checkBox4_CheckedChanged(object sender, EventArgs e)
        {
            //- Объединенный по проектам
            if (checkBox4.Checked == true)
            {
                flag_join = 1;
            }
            else
            {
                flag_join = 0;
            }
        }

        private void checkBox5_CheckedChanged(object sender, EventArgs e)
        {
            //- Общий отчёт
            if (checkBox5.Checked == true)
            {
                flag_all = 1;
            }
            else
            {
                flag_all = 0;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            // - Формирование файла
            Form5 form = new Form5();
            form.Show();
            CreateExcelFile();
            form.Close();
        }

        private Hashtable CheckExcellProcesses(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            int iCount = 0;
            foreach (Process ExcelProcess in AllProcesses)
            {
                myHashtable.Add(ExcelProcess.Id, iCount);
                iCount = iCount + 1;
            }
            return myHashtable;
        }

        private void KillExcel(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            foreach (Process ExcelProcess in AllProcesses)
            {
                if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
                    ExcelProcess.Kill();
            }
            AllProcesses = null;
        }


        private void CreateExcelFile()
        {
            //- Создать файл
            int i = 0;
            int j = 0;

            int fl_ex = 0;
            Hashtable myHashtable = new Hashtable();
            myHashtable = CheckExcellProcesses(myHashtable);
            fl_ex = 1;
            Excel.Application exapp = new Excel.Application();
            Excel.Workbook exwb = exapp.Workbooks.Add(System.Reflection.Missing.Value);
            //if (exwb.Sheets.Count > 1)
            //    for (i = exwb.Sheets.Count; i > 1; i--)
            //        ((Excel.Worksheet)exwb.Sheets[i]).Delete();
            exwb.Sheets.Add();

            fl_ex = 2;
            Excel.Worksheet exws1 = (Excel.Worksheet)exwb.Sheets[1];
            exws1.Name = dt_allrep[0].TableName;

            Excel.Worksheet exws2 = (Excel.Worksheet)exwb.Sheets[2];
            exws2.Name = dt_allrep[1].TableName;

            Excel.Worksheet exws3 = (Excel.Worksheet)exwb.Sheets[3];
            exws3.Name = dt_allrep[2].TableName;

            Excel.Worksheet exws4 = (Excel.Worksheet)exwb.Sheets[4];
            exws4.Name = dt_allrep[3].TableName;

            exapp.StandardFont = "Calibri";
            exapp.StandardFontSize = 11;

            exapp.DisplayAlerts = false;

            i = 0;
            j = 0;

            string fname = "Дифференциальное ценообразование";
            string filename = "";
                DialogResult dr = DialogResult.OK;
                if (filename == "")
                {
                    FileDialog sfd = new SaveFileDialog();
                    sfd.FileName = fname;
                    sfd.Filter = "Excel | *.xlsx";
                    dr = sfd.ShowDialog();
                    if (dr == DialogResult.OK) filename = sfd.FileName;
                    sfd = null;
                }
                List<System.Data.DataTable>[] DT_buf;

                DT_buf = new List<System.Data.DataTable>[4];
                DT_buf[0] = new List<System.Data.DataTable>();
                DT_buf[1] = new List<System.Data.DataTable>();
                DT_buf[2] = new List<System.Data.DataTable>();
                DT_buf[3] = new List<System.Data.DataTable>();
                i = 0;
                j = 0;

               if (dr == DialogResult.OK)
                    {
                        if (Path.GetExtension(filename) == "xlsx") Path.ChangeExtension(filename, "xlsx");
                    }
                    for (j = 0; j < DT_buf.Length; j++)
                    {
                        foreach (System.Data.DataTable dt in dt_allrep)
                        {
                            System.Data.DataTable newdt = dt.Copy();
                            DataView dv = newdt.DefaultView;
                            DT_buf[j].Add(dv.ToTable());
                            break;
                        }
                    }
                    i = 0;
                    j = 0;

                for (j = 0; j < dt_allrep[0].Columns.Count; ++j)
                    {
                        object start = 1;
                        object end = 50;
                        (exws1.Cells[1, j + 1] as Range).Value2 = dt_allrep[0].Columns[j].ColumnName;
                        (exws1.Cells[1, j + 1] as Range).get_Characters(start, end).Font.Color = XlRgbColor.rgbNavy;

                        for (i = 0; i < dt_allrep[0].Rows.Count; i++)
                        {
                            (exws1.Cells[i + 1, j + 1] as Range).Borders.Weight = 2;
                            (exws1.Cells[i + 2, j + 1] as Range).Borders.Weight = 2;
                            object Val = dt_allrep[0].Rows[i][j].ToString();
                            //if (Val != null)
                            {
                                (exws1.Cells[i + 2, j + 1] as Range).Value2 = Val.ToString();
                            }
                        }
                    }
                i = 0;
                j = 0;

                for (j = 0; j < dt_allrep[1].Columns.Count; ++j)
                {
                    object start = 1;
                    object end = 50;
                    (exws2.Cells[1, j + 1] as Range).Value2 = dt_allrep[1].Columns[j].ColumnName;
                    (exws2.Cells[1, j + 1] as Range).get_Characters(start, end).Font.Color = XlRgbColor.rgbNavy;

                    for (i = 0; i < dt_allrep[1].Rows.Count; i++)
                    {
                        (exws2.Cells[i + 1, j + 1] as Range).Borders.Weight = 2;
                        (exws2.Cells[i + 2, j + 1] as Range).Borders.Weight = 2;
                        object Val = dt_allrep[1].Rows[i][j].ToString();
                        //if (Val != null)
                        {
                            (exws2.Cells[i + 2, j + 1] as Range).Value2 = Val.ToString();
                        }
                    }
                }
                i = 0;
                j = 0;

                for (j = 0; j < dt_allrep[2].Columns.Count; ++j)
                {
                    object start = 1;
                    object end = 50;
                    (exws3.Cells[1, j + 1] as Range).Value2 = dt_allrep[2].Columns[j].ColumnName;
                    (exws3.Cells[1, j + 1] as Range).get_Characters(start, end).Font.Color = XlRgbColor.rgbNavy;

                    for (i = 0; i < dt_allrep[2].Rows.Count; i++)
                    {
                        (exws3.Cells[i + 1, j + 1] as Range).Borders.Weight = 2;
                        (exws3.Cells[i + 2, j + 1] as Range).Borders.Weight = 2;
                        object Val = dt_allrep[2].Rows[i][j].ToString();
                        //if (Val != null)
                        {
                            (exws3.Cells[i + 2, j + 1] as Range).Value2 = Val.ToString();
                        }
                    }
                }
                i = 0;
                j = 0;

                for (j = 0; j < dt_allrep[3].Columns.Count; ++j)
                {
                    object start = 1;
                    object end = 50;
                    (exws4.Cells[1, j + 1] as Range).Value2 = dt_allrep[3].Columns[j].ColumnName;
                    (exws4.Cells[1, j + 1] as Range).get_Characters(start, end).Font.Color = XlRgbColor.rgbNavy;

                    for (i = 0; i < dt_allrep[3].Rows.Count; i++)
                    {
                        (exws4.Cells[i + 1, j + 1] as Range).Borders.Weight = 2;
                        (exws4.Cells[i + 2, j + 1] as Range).Borders.Weight = 2;
                        object Val = dt_allrep[3].Rows[i][j].ToString();
                        //if (Val != null)
                        {
                            (exws4.Cells[i + 2, j + 1] as Range).Value2 = Val.ToString();
                        }
                    }
                }


                exwb.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                exwb.Close(Type.Missing, Type.Missing, Type.Missing);
                exapp.Quit();
                KillExcel(myHashtable);
        }

    }
}
